﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Windows.Forms;
using System.Threading;
using System.Data.SqlClient;
using DataSync.DAL;
using DataSync.Model;
using SpreadsheetGear.Drawing;
using System.Drawing;

namespace DataSync.BLL
{
    public delegate void ShowSyncProgress(string msg);
    public delegate void ClearSyncMsg();
    public delegate void ShowStatusInfo(string msg);

    public class SyncManage
    {
        private System.Windows.Forms.ListBox lbMsg;
        private ShowSyncProgress syncProgress;
        private ShowStatusInfo statusProgress;
        private ClearSyncMsg clearSyncMsg;

        private log4net.ILog log = null;

        private StatusStrip txtMsg;

        public StatusStrip TxtMsg
        {
            get { return txtMsg; }
            set { txtMsg = value; }
        }

        public ListBox LbMsg
        {
            get { return lbMsg; }
            set { lbMsg = value; }
        }

        public log4net.ILog Log
        {
            get { return log; }
            set { log = value; }
        }

        public SyncManage()
        {
            this.syncProgress = this.SetSyncProcessMsg;
            this.clearSyncMsg = this.ClearSyncMsg;
            this.statusProgress = this.SetStatusProcessMsg;
            this.log = log4net.LogManager.GetLogger("Debug"); //初始化
        }

        #region 完整数据同步
        public bool FullDataSync(SyncBase tableObj, SQLHelper sHelper, SQLHelper tHelper, Sync dataSync, SyncLog syncLog)
        {
            try
            {
                tHelper.BeginTransaction();

                dataSync.ClearTable(tableObj.TargetTableName, tHelper);

                syncLog.TableName = tableObj.TargetTableName;
                syncLog.MaxID = -2;
                syncLog.MaxTime = DateTime.Now;
                syncLog.StartTime = DateTime.Now;

                syncLog.Insert(tHelper, tableObj.SyncType);

                string syncMsg = "====================================================================================================";
                ShowSyncMsg(syncMsg);
                log.Info(syncMsg);

                syncMsg = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " ： 开始同步数据表 " + tableObj.SourceTableName;
                ShowSyncMsg(syncMsg);
                log.Info(syncMsg);

                string cmdText = "";

                if (tableObj.SourceTableName == "ACCOUNTS")
                {
                    cmdText = "SELECT A.USER_TYPE, A.USER_NO,[USER_NAME],C.USER_ADDRESS,C.USER_POST_CODE,USER_EMAIL,C.LINKMAN, "
                            + " C.USER_TELEPHONE_NO,USER_MOBILE_NO,C.USER_STATUS, C.IS_MESSAGE_ALERT, ACCOUNT_NO,ACCOUNT_TYPE, MAIN_CARD_NO,  "
                            + "  Account_BALANCE,  Account_LOW_MONEY,Account_CASH_LIMIT,ACCOUNT_STATUS FROM ACCOUNTS AS A LEFT JOIN CUSTOMERS C ON A.USER_NO = C.USER_NO ";
                }
                else
                {
                    cmdText = "SELECT * FROM " + tableObj.Scheme + "." + tableObj.SourceTableName;
                }

                if (tableObj.FilterString != "")
                {
                    cmdText += " WHERE " + tableObj.FilterString;
                }

                long rows = 0;
                object max = null;

                string syncTargetSql = "SELECT COUNT(1) FROM " + tableObj.TargetTableName;

                tableObj.BulkCopy = CreateSqlBulkCopy(tableObj, tHelper);

                bool ok = false;

                switch (tableObj.SourceTableName)
                {

                    case "ACCOUNTS":
                    case "CARD_ACCOUNTS":
                        ok = dataSync.SyncData(tableObj, cmdText, out rows, out max, sHelper, tHelper, syncTargetSql);
                        break;
                    default:
                        //ok = dataSync.SyncData(tableObj, cmdText, out rows, out max, tHelper, sHelper);
                        break;

                };

                syncLog.Rows = rows;
                syncLog.MaxID = -1;

                if (ok)
                {
                    syncLog.Update(tHelper, tableObj.SyncType);
                }
                tHelper.Commit();

                syncMsg = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " ： 数据表 " + tableObj.SourceTableName + " 同步完成，共同步 " + syncLog.Rows + " 条记录";
                ShowSyncMsg(syncMsg);
                log.Info(syncMsg);
                return true;
            }
            catch (Exception ex)
            {
                tHelper.Rollback();
                TraceHelper.WriteLine(ex);
                ShowSyncMsg(ex.Message);
                log.Info(ex);
                return false;
            }
        }
        #endregion

        #region 按日期增量同步
        public bool IncrementDataSyncByDate(SyncBase tableObj, SQLHelper sHelper, SQLHelper tHelper, Sync dataSync, SyncLog syncLog, DateTime? startTime, DateTime dbMaxTime)
        {
            try
            {
                string finishMsg = "";
                DateTime fromDate;
                DateTime? sFinishTime = this.GetSourceFinishTime();
                DateTime? tFinishTime = this.GetTargetFinishTime();
                if (!tableObj.IsCustomerSync)
                {
                    //不是自定义同步就把最大的时间指定为finish_time源表里面的时间
                    dbMaxTime = sFinishTime == null ? DateTime.Now : (DateTime)sFinishTime;
                    fromDate = tFinishTime == null ? DateTime.Parse(ConfigurationManager.AppSettings["FromDate"]).AddDays(-1) : (DateTime)tFinishTime;
                }
                else
                {
                    ShowSyncMsg(finishMsg);
                    fromDate = dbMaxTime;
                    dbMaxTime = dbMaxTime.AddDays(1);
                }


                //一般
                if (startTime == null)
                {
                    if (dbMaxTime.AddDays(-1).Subtract(fromDate.AddDays(-2)).Days < 0)
                    {
                        finishMsg = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " ： 数据表 " + tableObj.SourceTableName + " 没有增量数据需要同步，本次同步全部完成。";
                        ShowSyncMsg(finishMsg);
                        log.Info(finishMsg);
                        return true;
                    }
                    fromDate = fromDate.AddDays(-2);
                }
                else
                {
                    fromDate = (DateTime)startTime;
                }

                string syncSql = "";
                string syncTargetSql = "";
                while (dbMaxTime.AddDays(-1).Subtract(fromDate).Days > 1) //比较日志表中最大同步日期与源数据库系统当前日期差距，差1天时表示同步完成
                {
                    if (tableObj.IsSubTable)
                    {
                        tableObj.TargetTableName = tableObj.TargetTableName.Substring(0, tableObj.TargetTableName.LastIndexOf('_') + 1) + fromDate.AddDays(1).Month;
                    }

                    syncSql = "SELECT * FROM " + tableObj.Scheme + "." + "[" + tableObj.SourceTableName + "]";
                    syncSql += " WHERE " + tableObj.SourceDateFieldName + " >='" + fromDate.AddDays(1).ToString("yyyy-MM-dd") + "' AND " + tableObj.SourceDateFieldName + " < '" + fromDate.AddDays(2).ToString("yyyy-MM-dd") + "'";

                    if (tableObj.FilterString != "")
                    {
                        syncSql += " AND " + tableObj.FilterString;
                    }

                    syncTargetSql = "SELECT COUNT(1) FROM " + tableObj.TargetTableName + "";
                    syncTargetSql += " WHERE " + tableObj.TargetDateFieldName + " >='" + fromDate.AddDays(1).ToString("yyyy-MM-dd") + "' AND " + tableObj.TargetDateFieldName + " < '" + fromDate.AddDays(2).ToString("yyyy-MM-dd") + "'";
                    switch (tableObj.SourceTableName)
                    {
                        case "TRANSACTION":
                        //break;
                        case "ACCOUNT_OP_LOGS":
                            //case "ACCOUNT_MONTH":
                            if (tableObj.SourceTableName == "TRANSACTION" && fromDate.AddDays(1).Day == 1 && !tableObj.IsCustomerSync)
                            {
                                dataSync.ClearTable(tableObj.TargetTableName, tHelper);
                            }
                            else
                            {
                                ClearIllegalData(tableObj, fromDate.AddDays(1), tHelper);
                            }
                            SyncIncrementDate(tableObj, syncLog, dataSync, syncSql, fromDate.AddDays(1), sHelper, tHelper, syncTargetSql);
                            break;
                        default:
                            //SyncIncrementDate(tableObj, syncLog, dataSync, syncSql, fromDate.AddDays(1), sHelper, tHelper);
                            break;
                    };

                    fromDate = fromDate.AddDays(1);
                }

                finishMsg = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " ： 数据表 " + tableObj.SourceTableName + " 本次同步全部完成。";
                ShowSyncMsg(finishMsg);
                log.Info(finishMsg);
                return true;
            }
            catch (Exception ex)
            {
                TraceHelper.WriteLine(ex);
                ShowSyncMsg(ex.Message);
                log.Info(ex);
                return false;
            }
        }
        #endregion

        #region 月同步的基础
        /// <summary>
        /// 月同步的基础
        /// </summary>
        public void SyncIncrementDate(SyncBase tableObj, SyncLog syncLog, Sync dataSync, string syncSql, DateTime SyncDate, SQLHelper sHelper, SQLHelper tHelper, string syncTargetSql)
        {
            try
            {
                tHelper.BeginTransaction();

                syncLog.TableName = tableObj.TargetTableName;
                syncLog.MaxTime = SyncDate;
                syncLog.MaxID = 0; //不按自增ID同步
                syncLog.StartTime = DateTime.Now;

                syncLog.Insert(tHelper, tableObj.SyncType);

                string syncMsg = "====================================================================================================";
                ShowSyncMsg(syncMsg);
                log.Info(syncMsg);

                syncMsg = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " ： 开始同步数据表 " + tableObj.SourceTableName + " " + SyncDate.ToString("yyyy年MM月dd日") + "  的数据。";
                if (tableObj.SourceTableName == "ACCOUNT_MONTH")
                {
                    syncMsg = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " ： 开始同步数据表 " + tableObj.SourceTableName + " " + SyncDate.ToString("yyyy年MM月") + "  的数据。";
                }

                ShowSyncMsg(syncMsg);
                log.Info(syncMsg);

                long rows = 0;
                object max = null;

                bool ok = false;

                tableObj.BulkCopy = CreateSqlBulkCopy(tableObj, tHelper);

                ok = dataSync.SyncData(tableObj, syncSql, out rows, out max, sHelper, tHelper, syncTargetSql);


                syncMsg = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " ： 数据表 " + tableObj.SourceTableName + " " + SyncDate.ToString("yyyy年MM月dd日") + " 的数据" + " 同步完成，";

                if (tableObj.SourceTableName == "ACCOUNT_MONTH")
                {
                    syncMsg = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " ： 数据表 " + tableObj.SourceTableName + " " + SyncDate.ToString("yyyy年MM月") + " 的数据" + " 同步完成，";
                }

                syncMsg += "共同步" + rows + "条记录。";

                if (ok)
                {
                    ShowSyncMsg(syncMsg);
                    log.Info(syncMsg);
                    syncLog.Rows = rows;
                    syncLog.Update(tHelper, tableObj.SyncType);
                }
                tHelper.Commit();
            }
            catch (Exception ex)
            {
                tHelper.Rollback();
                TraceHelper.WriteLine(ex);
                ShowSyncMsg(ex.Message);
                log.Info(ex);
                throw ex;
            }
        }
        #endregion

        #region 按月份同步（需调试）
        /// <summary>
        /// 按月份同步（需调试）
        /// </summary>
        public bool IncrementDataSyncByMonth(SyncBase tableObj, SQLHelper sHelper, SQLHelper tHelper, Sync dataSync, SyncLog syncLog, DateTime? startTime, DateTime dbMaxTime)
        {
            try
            {
                string finishMsg = "";
                string syncSql = "";
                string syncTargetSql = "";
                bool isSyncFlag = false;
                //验证是否已经同步的sql语句
                string checkIsSyncSql = "";
                syncSql = "SELECT * FROM " + tableObj.Scheme + "." + "[" + tableObj.SourceTableName + "]";

                //获取日期过滤条件
                string syncWhereSql = " WHERE " + tableObj.SourceDateFieldName + " = '" + dbMaxTime.AddMonths(-1).Year + "-" + dbMaxTime.AddMonths(-1).Month + "-" + "1' ";
                //string syncWhereSql = " WHERE  STATISTICS_DATE = '2011-1-1' ";
                syncSql += syncWhereSql;

                if (tableObj.FilterString != "")
                {
                    syncSql += " AND " + tableObj.FilterString;
                }

                syncTargetSql = "SELECT COUNT(1) FROM " + tableObj.TargetTableName + "";
                syncTargetSql += " WHERE " + tableObj.TargetDateFieldName + " = '" + dbMaxTime.AddMonths(-1).Year + "-" + dbMaxTime.AddMonths(-1).Month + "-" + "1'";

                checkIsSyncSql = "SELECT COUNT(1) FROM " + tableObj.SourceTableName + "_RESULT " + syncWhereSql + " AND IS_AMENDED = 1 ";

                switch (tableObj.SourceTableName)
                {
                    case "ACCOUNT_MONTH":
                        //同步之前加上 _RESULT 表的验证
                        if (!IsSync(checkIsSyncSql, dataSync, sHelper))
                        {
                            //直接TruncateTable目标表（无论有无数据）
                            dataSync.ClearTable(tableObj.TargetTableName, tHelper);
                            SyncIncrementDate(tableObj, syncLog, dataSync, syncSql, dbMaxTime.AddMonths(-1), sHelper, tHelper, syncTargetSql);
                        }
                        else
                        {
                            isSyncFlag = true;
                            finishMsg = "提示 ： 月结单" + dbMaxTime.AddMonths(-1).Month + "月份数据账户不平，请手动处理之后再进行同步。";
                        }
                        break;

                    default:
                        //SyncIncrementDate(tableObj, syncLog, dataSync, syncSql, fromDate.AddDays(1), sHelper, tHelper);
                        break;
                };

                //是否已经同步
                if (!isSyncFlag)
                {
                    finishMsg = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " ： 数据表 " + tableObj.SourceTableName + " 本次同步全部完成。";
                }
                ShowSyncMsg(finishMsg);
                log.Info(finishMsg);
                return true;
            }
            catch (Exception ex)
            {
                TraceHelper.WriteLine(ex);
                ShowSyncMsg(ex.Message);
                log.Info(ex);
                return false;
            }
        }
        #endregion

        #region 是否已经同步并且没有处理已经同步后出现的错误
        /// <summary>
        /// 是否已经同步并且没有处理已经同步后出现的错误
        /// </summary>
        private bool IsSync(string checkIsSyncSql, Sync dataSync, SQLHelper sHelper)
        {
            return dataSync.IsSync(checkIsSyncSql, sHelper);
        }
        #endregion

        #region 清空不完整数据
        public void ClearIllegalData(SyncBase tableObj, DateTime targetDate, SQLHelper tHelper)
        {
            System.Type type = tableObj.GetType();
            SyncBase o = (SyncBase)Activator.CreateInstance(type);

            string selectText = "";
            selectText = "SELECT COUNT(1) AS NUM FROM " + tableObj.TargetTableName + " WHERE " + tableObj.TargetDateFieldName + ">='" + targetDate.ToString("yyyy-MM-dd") + "'" + " AND " + tableObj.TargetDateFieldName + "<'" + targetDate.AddDays(1).ToString("yyyy-MM-dd") + "'";

            if (tableObj.SourceTableName == "ACCOUNT_MONTH")
            {
                selectText = "SELECT COUNT(1) AS NUM FROM " + tableObj.TargetTableName + " WHERE " + tableObj.TargetDateFieldName + "='" + targetDate.ToString("yyyy-MM-dd") + "'";
            }

            try
            {
                int rows = int.Parse(tHelper.ExecuteScalar(selectText).ToString());
                if (rows > 0)
                {
                    string delText = "DELETE FROM " + tableObj.TargetTableName + " WHERE " + tableObj.TargetDateFieldName + " >='" + targetDate.ToString("yyyy-MM-dd") + "' "
                                    + " AND " + tableObj.TargetDateFieldName + "<'" + targetDate.AddDays(1).ToString("yyyy-MM-dd") + "'";

                    if (tableObj.SourceTableName == "ACCOUNT_MONTH")
                    {
                        delText = "DELETE FROM " + tableObj.TargetTableName + " WHERE " + tableObj.TargetDateFieldName + " ='" + targetDate.ToString("yyyy-MM-dd") + "' ";
                    }

                    tHelper.ExecuteSql(delText);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        public static string Object2String(object o)
        {
            if (o == null)
                return "NULL";
            else if (o.ToString() == string.Empty)
                return "NULL";
            else
                return "'" + o.ToString() + "'";
        }

        public static string Int2String(object o)
        {
            if (o == null)
                return "NULL";
            else if (o.ToString() == string.Empty)
                return "NULL";
            else
                return o.ToString();
        }

        public void ShowSyncMsg(object msg)
        {
            if (this.lbMsg.IsHandleCreated)
            {
                try
                {
                    this.lbMsg.Invoke(this.syncProgress, new object[] { msg });
                }
                catch (Exception ex)
                {
                    TraceHelper.WriteLine(ex);
                    log.Error(ex);
                }
            }
        }

        public void ShowStatusMsg(object msg)
        {
            if (this.TxtMsg.IsHandleCreated)
            {
                try
                {
                    this.TxtMsg.Invoke(this.statusProgress, new object[] { msg });
                }
                catch (Exception ex)
                {
                    //TraceHelper.WriteLine(ex);
                    log.Error(ex);
                }
            }
        }

        public void SetSyncProcessMsg(string msg)
        {
            this.lbMsg.Items.Add(msg);
        }

        public void SetStatusProcessMsg(string msg)
        {
            this.txtMsg.Items[0].Text = msg;
        }

        public void ClearMsg()
        {
            this.lbMsg.Invoke(this.clearSyncMsg);
        }

        public void ClearSyncMsg()
        {
            this.lbMsg.Items.Clear();
        }

        public void UpdateFinishTime()
        {
            string sConnString = System.Configuration.ConfigurationManager.ConnectionStrings["sConnString"].ConnectionString;
            string tConnString = System.Configuration.ConfigurationManager.ConnectionStrings["tConnString"].ConnectionString;
            SQLHelper sHelper = new SQLHelper(sConnString);
            SQLHelper tHelper = new SQLHelper(tConnString);

            try
            {
                DateTime currentTime = Convert.ToDateTime(sHelper.ExecuteScalar("SELECT GETDATE();"));
                string cmdText = "UPDATE WEB_ETC.dbo.FINISH_TIME SET [Date]='" + currentTime.ToShortDateString() + "' ," + " [TIME] = '" + currentTime.Hour + ":" + currentTime.Minute + ":" + currentTime.Second + "'";
                tHelper.ExecuteSql(cmdText);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                sHelper.Dispose();
                tHelper.Dispose();
            }
        }

        public DateTime? GetMaxDate(SQLHelper sqlHelper, string tableName, string fieldName)
        {
            string selectText = "SELECT MAX(" + fieldName + ") FROM " + tableName;
            object objTemp = sqlHelper.ExecuteScalar(selectText);

            if (objTemp == null || objTemp.ToString() == "")
                return null;
            else
                return DateTime.Parse(objTemp.ToString());
        }

        public void Dispose()
        {
            //selfReport.Dispose();
        }

        #region 创建表的SqlBulkCopy映射对象
        /// <summary>
        /// 创建表的SqlBulkCopy映射对象
        /// </summary>
        /// <param name="syncBase"></param>
        /// <param name="sqlHelper"></param>
        /// <returns></returns>
        public SqlBulkCopy CreateSqlBulkCopy(SyncBase syncBase, SQLHelper sqlHelper)
        {
            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlHelper.SqlCon, SqlBulkCopyOptions.KeepIdentity, sqlHelper.SqlTrans);
            sqlBulkCopy.BatchSize = int.Parse(System.Configuration.ConfigurationManager.AppSettings["BatchSize"].ToString());
            sqlBulkCopy.DestinationTableName = "" + syncBase.TargetTableName + "";
            sqlBulkCopy.BulkCopyTimeout = 0;

            switch (syncBase.SourceTableName)
            {

                case "TRANSACTION":
                    sqlBulkCopy.ColumnMappings.Add("MID", "MID");
                    sqlBulkCopy.ColumnMappings.Add("CARD_TYPE", "CARD_TYPE");
                    sqlBulkCopy.ColumnMappings.Add("ICCARD_NO", "ICCARD_NO");
                    sqlBulkCopy.ColumnMappings.Add("VEHICLE_CLASS", "VEHCLASS");
                    sqlBulkCopy.ColumnMappings.Add("ISSUER_NUM", "ISSUER_NUM");

                    sqlBulkCopy.ColumnMappings.Add("TRANSACTION_TYPE", "TRANS_TYPE");
                    sqlBulkCopy.ColumnMappings.Add("CASH", "CASH");
                    sqlBulkCopy.ColumnMappings.Add("IC_TRANSACTION_TIME", "IC_TRANS_TIME");
                    sqlBulkCopy.ColumnMappings.Add("BALANCE", "BALANCE");
                    sqlBulkCopy.ColumnMappings.Add("CHARGE_CASH", "CHARGE_CASH");
                    sqlBulkCopy.ColumnMappings.Add("PROVIDER_ID", "PROVIDER_ID");

                    sqlBulkCopy.ColumnMappings.Add("LAST_BALANCE", "LAST_BALANCE");
                    sqlBulkCopy.ColumnMappings.Add("CHARGE_TIME", "CHARGE_TIME");
                    sqlBulkCopy.ColumnMappings.Add("DESCRIPTION", "DESCRIPTION");
                    sqlBulkCopy.ColumnMappings.Add("CAR_SERIAL", "CAR_SERIAL");
                    sqlBulkCopy.ColumnMappings.Add("CHARGE_STATUS", "CHARGE_STATUS");
                    sqlBulkCopy.ColumnMappings.Add("ACCOUNT_NO", "ACCOUNT_NO");
                    break;

                case "ACCOUNT_OP_LOGS":
                    sqlBulkCopy.ColumnMappings.Add("ID", "ID");
                    sqlBulkCopy.ColumnMappings.Add("ACCOUNT_NO", "ACCOUNT_NO");
                    sqlBulkCopy.ColumnMappings.Add("CARD_NO", "CARD_NO");
                    sqlBulkCopy.ColumnMappings.Add("TRANSACTION_TYPE", "TRANS_TYPE");
                    sqlBulkCopy.ColumnMappings.Add("IN_MONEY", "IN_MONEY");

                    sqlBulkCopy.ColumnMappings.Add("OUT_MONEY", "OUT_MONEY");
                    sqlBulkCopy.ColumnMappings.Add("TRANSACTION_TIME", "TRANS_TIME");
                    sqlBulkCopy.ColumnMappings.Add("LOAD_FROM", "LOAD_FROM");
                    sqlBulkCopy.ColumnMappings.Add("SERVICE_FEE_IN_MONEY", "SERVICE_FEE_IN_MONEY");
                    sqlBulkCopy.ColumnMappings.Add("SERVICE_FEE_OUT_MONEY", "SERVICE_FEE_OUT_MONEY");

                    sqlBulkCopy.ColumnMappings.Add("SERVICE_FEE_TYPE", "SERVICE_FEE_TYPE");
                    sqlBulkCopy.ColumnMappings.Add("SERVICE_FEE_INVOICE", "SERVICE_FEE_INVOICE");
                    sqlBulkCopy.ColumnMappings.Add("OLD_BALANCE", "OLD_BALANCE");
                    sqlBulkCopy.ColumnMappings.Add("NEW_BALANCE", "NEW_BALANCE");
                    sqlBulkCopy.ColumnMappings.Add("RESUME", "RESUME");

                    break;

                case "ACCOUNT_MONTH":
                    sqlBulkCopy.ColumnMappings.Add("STATISTICS_DATE", "STAT_DATE");
                    sqlBulkCopy.ColumnMappings.Add("ACCOUNT_NO", "ACCOUNT_NO");
                    sqlBulkCopy.ColumnMappings.Add("USER_TYPE", "USER_TYPE");
                    sqlBulkCopy.ColumnMappings.Add("USER_NO", "USER_NO");
                    sqlBulkCopy.ColumnMappings.Add("ACCOUNT_TYPE", "ACCOUNT_TYPE");

                    sqlBulkCopy.ColumnMappings.Add("DEPOSIT_TYPE", "DEPOSIT_TYPE");
                    sqlBulkCopy.ColumnMappings.Add("PREVIOUS_BALANCE", "PREV_BALANCE");
                    sqlBulkCopy.ColumnMappings.Add("END_BALANCE", "BALANCE");
                    sqlBulkCopy.ColumnMappings.Add("INCOME", "INCOME");
                    sqlBulkCopy.ColumnMappings.Add("PAY", "PAY");

                    sqlBulkCopy.ColumnMappings.Add("REFUND", "REFUND");
                    sqlBulkCopy.ColumnMappings.Add("VERSION", "VERSION");
                    sqlBulkCopy.ColumnMappings.Add("REFILL", "REFILL");
                    sqlBulkCopy.ColumnMappings.Add("SPECIAL_PAY", "SPECIALPAY");
                    sqlBulkCopy.ColumnMappings.Add("PARKING_PAY", "PARKING_PAY");

                    break;

                case "ACCOUNTS":
                    sqlBulkCopy.ColumnMappings.Add("USER_TYPE", "USER_TYPE");
                    sqlBulkCopy.ColumnMappings.Add("USER_NO", "USER_NO");
                    sqlBulkCopy.ColumnMappings.Add("USER_NAME", "USER_NAME");
                    sqlBulkCopy.ColumnMappings.Add("USER_ADDRESS", "USER_ADDRESS");
                    sqlBulkCopy.ColumnMappings.Add("USER_POST_CODE", "POST_CODE");
                    sqlBulkCopy.ColumnMappings.Add("USER_EMAIL", "E_MAIL");
                    sqlBulkCopy.ColumnMappings.Add("LINKMAN", "LINKMAN");
                    sqlBulkCopy.ColumnMappings.Add("USER_TELEPHONE_NO", "TELEPHONE_NO");
                    sqlBulkCopy.ColumnMappings.Add("USER_MOBILE_NO", "MOBILE_NO");
                    sqlBulkCopy.ColumnMappings.Add("USER_STATUS", "USER_STATUS");

                    sqlBulkCopy.ColumnMappings.Add("IS_MESSAGE_ALERT", "SHORT_MESSAGE_ALERT");
                    sqlBulkCopy.ColumnMappings.Add("ACCOUNT_NO", "ACCOUNT_NO");
                    sqlBulkCopy.ColumnMappings.Add("ACCOUNT_TYPE", "ACCOUNT_TYPE");
                    sqlBulkCopy.ColumnMappings.Add("MAIN_CARD_NO", "MAIN_CARD_NO");
                    sqlBulkCopy.ColumnMappings.Add("ACCOUNT_BALANCE", "BALANCE");
                    sqlBulkCopy.ColumnMappings.Add("ACCOUNT_LOW_MONEY", "LOW_MONEY");
                    sqlBulkCopy.ColumnMappings.Add("ACCOUNT_CASH_LIMIT", "CASH_LIMIT");
                    sqlBulkCopy.ColumnMappings.Add("ACCOUNT_STATUS", "ACCOUNT_STATUS");

                    break;

                case "CARD_ACCOUNTS":
                    sqlBulkCopy.ColumnMappings.Add("ID", "ID");
                    sqlBulkCopy.ColumnMappings.Add("CARD_NO", "CARD_NO");
                    sqlBulkCopy.ColumnMappings.Add("ACCOUNT_NO", "ACCOUNT_NO");
                    sqlBulkCopy.ColumnMappings.Add("MAIN_CARD_ID", "MAIN_CARD_ID");
                    sqlBulkCopy.ColumnMappings.Add("PRIMARY_CARD", "PRIMARY_CARD");

                    sqlBulkCopy.ColumnMappings.Add("CARD_TYPE", "CARD_TYPE");
                    sqlBulkCopy.ColumnMappings.Add("CARD_SIGNED_DATE", "CARD_SIGNED_DATE");
                    sqlBulkCopy.ColumnMappings.Add("CARD_EXPIRED_DATE", "CARD_EXPIRED_DATE");
                    sqlBulkCopy.ColumnMappings.Add("REGISTE_DATE", "REGISTE_DATE");
                    sqlBulkCopy.ColumnMappings.Add("CARD_STATUS", "CARD_STATUS");

                    sqlBulkCopy.ColumnMappings.Add("MODIFY_DATE", "MODIFY_DATE");
                    sqlBulkCopy.ColumnMappings.Add("ISSUE_STATUS", "ISSUE_STATUS");

                    break;


            };

            return sqlBulkCopy;
        }
        #endregion

        public DateTime? GetSourceFinishTime()
        {
            string sConnString = System.Configuration.ConfigurationManager.ConnectionStrings["sConnString"].ConnectionString;
            SQLHelper sHelper = new SQLHelper(sConnString);
            string sql = "SELECT DATE FROM FINISH_TIME";
            Object sObj = sHelper.ExecuteScalar(sql);
            return sObj == DBNull.Value || sObj == null ? null : (DateTime?)Convert.ToDateTime(sObj);
        }

        public DateTime? GetTargetFinishTime()
        {
            string tConnString = System.Configuration.ConfigurationManager.ConnectionStrings["tConnString"].ConnectionString;
            SQLHelper tHelper = new SQLHelper(tConnString);
            string sql = "SELECT DATE FROM FINISH_TIME";
            Object tObj = tHelper.ExecuteScalar(sql);
            return tObj == DBNull.Value || tObj == null ? null : (DateTime?)Convert.ToDateTime(tObj);
        }
    }
}
